﻿///#undef MySql
///#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class CheckingCashFlow : WebList_SXD ///System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
            // 操作权限的判断
            Common_SXD.Is_LongIn(Response, Session, Request);

            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            ///bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);

            //if(bIs_Admin != 1)
            if (!bIs_O_M && !bIs_Admin && !bIs_Collector && !bIs_Counsellor)
            { // 只有顾问、O. M.、和收款员能使用该功能。
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，请用其他身份登录！");
                Response.Write("</fong></h2>");
                Response.End();
            }

            switch (Session["Office"].ToString().Trim())
            {
                // 开放顾问权限的办公室 
                case "上海":
                    if (!bIs_O_M && !bIs_Admin && !bIs_Collector && !bIs_Accountant &&
                        !bIs_Counsellor)
                    { // 判断当前用户是否为管理员
                        Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                        Response.Write("<br>对不起，只有顾问、O. M.、收款员和总部会计才能使用该功能。");
                        Response.Write("</fong></h2>");
                        Response.End();
                    }

                    break;

                // 关闭顾问限
                case "北京":
                case "南京":
                case "杭州":
                case "宁波":
                case "武汉":
                case "重庆":
                case "成都":
                case "广州":
                case "青岛":
                case "BD":
                case "易申网":
                case "北京IDP":
                case "上海IDP":
                case "广州IDP":
                case "南京IDP":
                case "深圳IDP":
                    if (!bIs_O_M && !bIs_Admin && !bIs_Collector && !bIs_Accountant)
                    {
                        Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                        Response.Write("<br>对不起，只有O. M.、收款员和总部会计才能使用该功能。");
                        Response.Write("</fong></h2>");
                        Response.End();
                    }
                    break;

                default:
                    break;


            }

            ///////////// 读出资金流向表的年份
            string tempYear = Request["Year"];
            Session["Year_Insert"] = tempYear.Trim();
            //			Session["Year_Insert"] = Request["Year"]; 

            if (!IsPostBack)
            {
                Init_Table_Cheking_First_Payment_Contract();
                Textbox_LockDay_LastMonth.Text = Common_SXD.get_LockDay_LastMonth(Textbox_LockDay_LastMonth);
                Textbox_LockDay_LastMonth.Enabled = false;

                
            }

        }

        protected void Bt_Search_Click(object sender, EventArgs e)
        {
            // 点击查询按钮后，重新绑定数据
            Init_Table_Cheking_First_Payment_Contract();
            Init_Table_Cheking_Second_Payment_Contract();

        }

        private void Init_Table_Cheking_First_Payment_Contract()
        //		private void Init_Table_Contract()
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            /*
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象
            // 包含2个 Select 语句的 SQL，能返回2个结果集
            */
            string sSql;
            if (/*bIs_Receptionist ||*/ bIs_Writer || bIs_Counsellor)
            {
#if MSSql
                sSql = "select " + Top_MSSql("100") + " Series_No, Counsellor, Writer, Student_Name, Pinyi, No_Contract, Destinations_OS + ':' + Student_Type as 'Student_Type', YearMonth_Start, Remitter, Date_Second_Receipt, Notes, Date_First_Payment, Date_Second_Payment, Date_Completion_Contract, Termination_Refund, Date_Termination_Refund, No_Origin from contract where  (Date_First_Payment <= '1930-1-1' or Is_New = 1 ) and (";
#elif MySql
                sSql = "select " + Top_MSSql("100") + " Series_No, Counsellor, Writer, Student_Name, Pinyi, No_Contract, CONCAT(Destinations_OS, ':', Student_Type) as 'Student_Type', YearMonth_Start, Remitter, Channel, Receipt, Notes, Date_First_Receipt, Date_First_Payment, Date_Second_Receipt, Date_Second_Payment, Date_Completion_Contract, Termination_Refund, Date_Termination_Refund, No_Origin from contract where  (Date_First_Payment <= '1930-1-1' or Is_New = 1 ) and (";
#endif
            }
            else
            {
#if MSSql
                sSql = "select " + Top_MSSql("100") + "Series_No, Counsellor, Writer, Student_Name, Pinyi, No_Contract, Destinations_OS + ':' + Student_Type as 'Student_Type', YearMonth_Start, Remitter, Date_Second_Receipt, Notes, Amount, First_Payment, Date_First_Payment, Second_Payment, Date_Second_Payment, Date_Completion_Contract, Termination_Refund, Date_Termination_Refund, No_Origin from contract where  (Date_First_Payment <= '1930-1-1' or Is_New = 1 ) and (";
#elif MySql
                sSql = "select " + Top_MSSql("100") + "Series_No, Counsellor, Writer, Student_Name, Pinyi, No_Contract, CONCAT(Destinations_OS, ':', Student_Type) as 'Student_Type', YearMonth_Start, Remitter, Channel, Receipt, Notes, Amount, First_Payment, Date_First_Receipt, Date_First_Payment, Second_Payment, Date_Second_Receipt, Date_Second_Payment, Date_Completion_Contract, Termination_Refund, Date_Termination_Refund, No_Origin from contract where  (Date_First_Payment <= '1930-1-1' or Is_New = 1 ) and (";
#endif
            }
            sSql += " Office = '" + Session["Office"] + "') and ";

            if (bIs_Counsellor)
                if (!bIs_O_M)
                    if (!bIs_Admin)
                        if (!bIs_Collector)
                            sSql += "( Counsellor = '" + Session["C_N_Employee"] + "' or  Writer = '" + Session["C_N_Employee"] + "' ) and ";

            // 滤除其他年份的数据
            int previousYear = Convert.ToInt16(Session["Year_Insert"]) - 1;
            /// string s_priviousYear = Convert.ToString(previousYear).Trim();

            sSql += "(  Series_No like '" + Session["Year_Insert"] + "%' or  Series_No like '" + Convert.ToString(previousYear).Trim() + "%') and ";

            sSql += "(  Student_Name  like '%" + sSearchValue + "%' ";
            sSql += "or  Series_No  like '%" + sSearchValue + "%' ";
            sSql += "or  Notes  like '%" + sSearchValue + "%' ";
            sSql += "or  Remitter  like '%" + sSearchValue + "%' ";
            sSql += "or  Ref_Cash_No  like '%" + sSearchValue + "%' ";
            sSql += "or  Pinyi  like '%" + sSearchValue + "%' ";
            sSql += "or  No_Contract  like '%" + sSearchValue + "%' )";

            sSql += " order by Series_No DESC";
#if MSSql
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;
#elif MySql
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;
#endif

            //清除表中的内容
            Table_Cheking_First_Payment.Rows.Clear();

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                    string temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("修改"));
                    row.Cells.Add(cell);
                }
                Table_Cheking_First_Payment.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series = sqlReader.GetString(0);
#elif MySql
                String sNo_Series = MysqlReader.GetString(0);
#endif
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();

                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=ReadOnly:" +
                        sNo_Series.Trim() +
                        "',900,620);\">只读" +
                        sNo_Series.Trim() +
                        "</a>"));
                    row.Cells.Add(cell);
                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    if (bIs_Collector || bIs_Counsellor)
                    {//////收款员/顾问能修改数据
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=" +
                            sNo_Series.Trim() +
                            "',900,620);\">一期款</a>"));
                        row.Cells.Add(cell);
                    }
                }

                Table_Cheking_First_Payment.Rows.Add(row);
                j++;
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
#endif
        }

        private void Init_Table_Cheking_Second_Payment_Contract()
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////

            //清除表中的内容
            Table_Cheking_Second_Payment.Rows.Clear();
            if (!CheckBox_Cheking_Second_Payment.Checked)
                return;

            string sSearchValue = TextBox_SearchValue.Text.Trim();

            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Collector = Convert.ToBoolean(Session["Is_Collector"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);

#if MSSql
            string sSql = "select " + Top_MSSql("100") + " Series_No, Counsellor, Writer, Student_Name, Pinyi, No_Contract, Destinations_OS + ':' + Student_Type as 'Student_Type', YearMonth_Start, Remitter, Date_Second_Receipt, Notes, Amount, First_Payment, Date_First_Payment, Second_Payment, Date_Second_Payment, Date_Completion_Contract, Termination_Refund, Date_Termination_Refund, No_Origin from contract where ";
#elif MySql
            string sSql = "select " + Top_MSSql("100") + " Series_No, Counsellor, Writer, Student_Name, Pinyi, No_Contract, CONCAT(Destinations_OS, ':', Student_Type) as 'Student_Type', YearMonth_Start, Remitter, Channel, Receipt, Notes, Amount, First_Payment, Date_First_Receipt, Date_First_Payment, Second_Payment, Date_Second_Receipt, Date_Second_Payment, Date_Completion_Contract, Termination_Refund, Date_Termination_Refund, No_Origin from contract where ";
#endif

            
            
            sSql += " (Date_Second_Payment <= '1930-1-1' and Second_Payment > 0 and Date_First_Payment > '1930-1-1') and ";
            sSql += "( Office = '" + Session["Office"] + "') and ";

            if (bIs_Counsellor)
                if (!bIs_O_M)
                    if (!bIs_Admin)
                        if (!bIs_Collector)
                            sSql += "( Counsellor = '" + Session["C_N_Employee"] + "' or  Writer = '" + Session["C_N_Employee"] + "' ) and ";

            // 滤除其他年份的数据
            sSql += " Series_No like '" + Session["Year_Insert"] + "%' and ";

            sSql += "(  Student_Name  like '%" + sSearchValue + "%' ";
            sSql += "or  Series_No  like '%" + sSearchValue + "%' ";
            sSql += "or  Notes  like '%" + sSearchValue + "%' ";
            sSql += "or  Remitter  like '%" + sSearchValue + "%' ";
            sSql += "or  Ref_Cash_No  like '%" + sSearchValue + "%' ";
            sSql += "or  Pinyi  like '%" + sSearchValue + "%' ";
            sSql += "or  No_Contract  like '%" + sSearchValue + "%' )";

            sSql += " order by Series_No DESC";

#if MSSql
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;
#elif MySql
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;
#endif
            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                    string temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("修改"));
                    row.Cells.Add(cell);
                }
                Table_Cheking_Second_Payment.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series = sqlReader.GetString(0);
#elif MySql
                String sNo_Series = MysqlReader.GetString(0);
#endif
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=ReadOnly:" +
                        sNo_Series.Trim() +
                        "',900,620);\">只读" +
                        sNo_Series.Trim() +
                        "</a>"));
                    row.Cells.Add(cell);
                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    //				if(bIs_Collector)
                    //				{//////只有收款员能修改数据
                    //					cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=" + 
                    //						sNo_Series.Trim() + 
                    //						"',900,420);\">一期款</a>"));
                    //					row.Cells.Add(cell);
                    //				}
                    if (bIs_Collector || bIs_Counsellor)
                    {//////只有收款员能修改数据
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=EditContractSecondPay:" +
                            sNo_Series.Trim() +
                            "',900,620);\">二期款/执行情况</a>"));
                        row.Cells.Add(cell);
                    }

                }

                Table_Cheking_Second_Payment.Rows.Add(row);
                j++;
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
            
#endif
        }

    }
}
